package com.campfire.intelligentsecurity.business.service;

import cn.hutool.core.util.StrUtil;
import cn.hutool.http.HttpUtil;
import cn.hutool.json.JSONObject;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import com.campfire.intelligentsecurity.business.bean.*;
import com.campfire.intelligentsecurity.sys.bean.ListPager;
import com.campfire.intelligentsecurity.sys.bean.QueryMap;
import com.campfire.intelligentsecurity.sys.service.BaseService;
import com.github.drinkjava2.jsqlbox.DbContext;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.core.env.Environment;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import static com.github.drinkjava2.jsqlbox.DB.*;

@Service
public class BusOperationplanService extends BaseService {
    @Autowired
    @Qualifier("main")
    DbContext dbContext;

    @Autowired
    HttpSession session;
    HttpServletResponse response;

    @Autowired
    Environment environment;
    @Autowired
    CameraControlService cameraControlService;

    public Object List(BusOperationplan busOperationplan) {
        StringBuffer sql = new StringBuffer();
        sql.append("select ");
        sql.append("t1.ID, ");
        sql.append("t1.JOB_CONTENT, ");
        sql.append("t1.START_TIME, ");
        sql.append("t1.END_TIME, ");
        sql.append("t1.COORDINATE, ");
        sql.append("t1.OPERATION_LOCATION, ");
        sql.append("t1.WORKING_TYPE, ");
        sql.append("t1.IS_ELECTRIFIED, ");
        sql.append("t1.CONSTRUCTION_MANAGEMENT_UNIT, ");
        sql.append("t1.CONSTRUCTION_UNIT, ");
        sql.append("s1.LABEL AS sfdd, ");
        sql.append("(select GROUP_CONCAT(CONCAT('''',p1.person_id,'''') SEPARATOR ',') from bus_operationplan_person p1 where p1.operation_id=t1.ID) as zyryid, ");
        sql.append("(select count(p2.id) from bus_operationplan_camera p2 where p2.operation_id=t1.ID) as sxtsl, ");
        sql.append("t1.SAFETY_CHARGE_PERSON_ID AS safetyChargePersonId, ");
        sql.append("o1.pname AS aqjdr, ");
        sql.append("t1.SPEICAL_PERSON AS zzjhr, ");
        sql.append("t1.OPERATION_CHARGE_PERSON_ID AS operationChargePersonId, ");
        sql.append("o2.pname AS gzfzr, ");
        sql.append("t1.OPERATIONAL_RISK_LEVEL AS operationalRiskLevel, ");
        sql.append("s2.LABEL AS zyfxdj, ");
        sql.append("t1.POWER_RISK_LEVEL AS powerRiskLevel, ");
        sql.append("s3.LABEL AS dwfxdj, ");
        sql.append("t1.STATUS AS status, ");
        sql.append("s4.LABEL AS zyzt, ");
        sql.append("(select count(1) from t_event_record t where t.planid=t1.ID and t.state='1') wgCount, ");
        sql.append("(select count(1) from t_event_record t where t.planid=t1.ID and t.state='0') dqrwgCount, ");
        sql.append("(select GROUP_CONCAT(a.algorithm_id SEPARATOR ',') from bus_operationplan_algorithm a where a.operation_id=t1.ID) as algorithms, ");
        sql.append("c.cameras, c.cameras_name ");
        sql.append("FROM ");
        sql.append("bus_operationplan t1  ");
        sql.append("LEFT JOIN t_sys_dict s1 on s1.`VALUE`=t1.IS_ELECTRIFIED and s1.TYPE='dict_sf' ");
        sql.append("LEFT JOIN t_sys_dict s2 on s2.`VALUE`=t1.OPERATIONAL_RISK_LEVEL and s2.TYPE='dict_zyfxdj' ");
        sql.append("LEFT JOIN t_sys_dict s3 on s3.`VALUE`=t1.POWER_RISK_LEVEL and s3.TYPE='dict_dwfxdj' ");
        sql.append("LEFT JOIN t_sys_dict s4 on s4.`VALUE`=t1.STATUS and s4.TYPE='dict_status' ");
        sql.append("LEFT JOIN bus_person o1 on o1.ID=t1.SAFETY_CHARGE_PERSON_ID ");
        sql.append("LEFT JOIN bus_person o2 on o2.ID=t1.OPERATION_CHARGE_PERSON_ID ");
        sql.append("LEFT JOIN ( ");
        sql.append("        select operation_id, GROUP_CONCAT(camera_num SEPARATOR ',') cameras, GROUP_CONCAT(camera_name SEPARATOR ',') cameras_name ");
        sql.append("        from bus_operationplan_camera group by operation_id) c on c.operation_id=t1.ID ");
        sql.append("WHERE ");
        sql.append("1 =1 and t1.DEL_FLAG='0'");
        QueryMap userParams = getUserParams();
        ListPager pager = new ListPager();
        List<BusOperationplan> data = new ArrayList<>();
        long count = 0;
        int pageSize = userParams.getInt("limit");
        int currentPage = userParams.getInt("page");
        String zyry = null;
        if (busOperationplan != null) {
            data = dbContext.qryEntityList(pagin(currentPage, pageSize), sql,
                    noBlank(" and t1.OPERATION_LOCATION like ? ", "%", busOperationplan.getOperationLocation(), "%"),
                    noBlank(" and t1.STATUS=?", busOperationplan.getStatus()),
                    " order by t1.CREATE_DATE desc", BusOperationplan.class);
            count = dbContext.qryLongValue("select count(1) from (", sql,
                    noBlank(" and t1.OPERATION_LOCATION like ? ", "%", busOperationplan.getOperationLocation(), "%"),
                    noBlank(" and t1.STATUS=? ", busOperationplan.getStatus()), ") a");
        }
        pager.setCount(count);
        pager.setData(data);
        return pager;
    }

    public Object ListPlan(BusOperationplan busOperationplan) {
        StringBuffer sql = new StringBuffer();
        sql.append("select ");
        sql.append("t1.ID, ");
        sql.append("t1.JOB_CONTENT, ");
        sql.append("t1.START_TIME, ");
        sql.append("t1.END_TIME, ");
        sql.append("t1.COORDINATE, ");
        sql.append("t1.OPERATION_LOCATION, ");
        sql.append("t1.WORKING_TYPE, ");
        sql.append("t1.IS_ELECTRIFIED, ");
        sql.append("t1.CONSTRUCTION_MANAGEMENT_UNIT, ");
        sql.append("t1.CONSTRUCTION_UNIT, ");
        sql.append("s1.LABEL AS sfdd, ");
        sql.append("(select GROUP_CONCAT(CONCAT('''',p1.person_id,'''') SEPARATOR ',') from bus_operationplan_person p1 where p1.operation_id=t1.ID) as zyryid, ");
        sql.append("(select count(p2.id) from bus_operationplan_camera p2 where p2.operation_id=t1.ID) as sxtsl, ");
        sql.append("t1.SAFETY_CHARGE_PERSON_ID AS safetyChargePersonId, ");
        sql.append("o1.pname AS aqjdr, ");
        sql.append("t1.SPEICAL_PERSON AS zzjhr, ");
        sql.append("t1.OPERATION_CHARGE_PERSON_ID AS operationChargePersonId, ");
        sql.append("o2.pname AS gzfzr, ");
        sql.append("t1.OPERATIONAL_RISK_LEVEL AS operationalRiskLevel, ");
        sql.append("s2.LABEL AS zyfxdj, ");
        sql.append("t1.POWER_RISK_LEVEL AS powerRiskLevel, ");
        sql.append("s3.LABEL AS dwfxdj, ");
        sql.append("t1.STATUS AS status, ");
        sql.append("s4.LABEL AS zyzt, ");
        sql.append("(select count(1) from t_event_record t where t.planid=t1.ID and t.state='1') wgCount, ");
        sql.append("(select count(1) from t_event_record t where t.planid=t1.ID and t.state='0') dqrwgCount, ");
        sql.append("(select GROUP_CONCAT(a.algorithm_id SEPARATOR ',') from bus_operationplan_algorithm a where a.operation_id=t1.ID) as algorithms, ");
        sql.append("c.cameras, c.cameras_name ");
        sql.append("FROM ");
        sql.append("bus_operationplan t1  ");
        sql.append("LEFT JOIN t_sys_dict s1 on s1.`VALUE`=t1.IS_ELECTRIFIED and s1.TYPE='dict_sf' ");
        sql.append("LEFT JOIN t_sys_dict s2 on s2.`VALUE`=t1.OPERATIONAL_RISK_LEVEL and s2.TYPE='dict_zyfxdj' ");
        sql.append("LEFT JOIN t_sys_dict s3 on s3.`VALUE`=t1.POWER_RISK_LEVEL and s3.TYPE='dict_dwfxdj' ");
        sql.append("LEFT JOIN t_sys_dict s4 on s4.`VALUE`=t1.STATUS and s4.TYPE='dict_status' ");
        sql.append("LEFT JOIN bus_person o1 on o1.ID=t1.SAFETY_CHARGE_PERSON_ID ");
        sql.append("LEFT JOIN bus_person o2 on o2.ID=t1.OPERATION_CHARGE_PERSON_ID ");
        sql.append("LEFT JOIN ( ");
        sql.append("        select operation_id, GROUP_CONCAT(camera_num SEPARATOR ',') cameras, GROUP_CONCAT(camera_name SEPARATOR ',') cameras_name ");
        sql.append("        from bus_operationplan_camera group by operation_id) c on c.operation_id=t1.ID ");
        sql.append("WHERE ");
        sql.append("1 =1 and t1.DEL_FLAG='0' and t1.id!='-1' ");
        QueryMap userParams = getUserParams();
        ListPager pager = new ListPager();
        List<BusOperationplan> data = new ArrayList<>();
        long count = 0;
        int pageSize = userParams.getInt("limit");
        int currentPage = userParams.getInt("page");
        String zyry = null;
        if (busOperationplan != null) {
            data = dbContext.qryEntityList(pagin(currentPage, pageSize), sql,
                    noBlank(" and t1.OPERATION_LOCATION like ? ", "%", busOperationplan.getOperationLocation(), "%"),
                    noBlank(" and t1.STATUS=?", busOperationplan.getStatus()),
                    " order by t1.CREATE_DATE desc", BusOperationplan.class);
            count = dbContext.qryLongValue("select count(1) from (", sql,
                    noBlank(" and t1.OPERATION_LOCATION like ? ", "%", busOperationplan.getOperationLocation(), "%"),
                    noBlank(" and t1.STATUS=? ", busOperationplan.getStatus()), ") a");
            for (BusOperationplan plan : data) {
                if (plan.getZyryid() != null) {
                    zyry = dbContext.qryString("select GROUP_CONCAT(pname SEPARATOR ',') from bus_person where id in (" + plan.getZyryid() + ")");
                    plan.setZyryid(zyry);
                }
            }
        }
        pager.setCount(count);
        pager.setData(data);
        return pager;
    }

    public Object saveOrUpdate(BusOperationplan entity) {
        String user = (String) session.getAttribute("usercode");
        String sj = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(entity.getEndTime());
        sj=sj.replace("00:00:00","23:59:59");
        entity.setEndTime(Timestamp.valueOf(sj));
        if (StrUtil.isNotEmpty(entity.getId())) {
            entity.setUpdateBy(user);
            entity.update();
            //删除作业计划相关人员和设备
            dbContext.exe("delete from bus_operationplan_person where operation_id =?", par(entity.getId()));
            dbContext.exe("delete from bus_operationplan_camera where operation_id =?", par(entity.getId()));
            //保存作业计划相关人员
            BusOperationplanPerson person = null;
            String[] zyry = entity.getZyryid().split(",");
            for (int i = 0; i < zyry.length; i++) {
                person = new BusOperationplanPerson();
                person.setOperationId(entity.getId());
                person.setStatus("1");
                person.setPersonId(zyry[i]);
                person.insert();
            }
            //保存作业计划相关监控设备
            BusOperationplanCamera camera = null;
            String[] jksb = entity.getZysbjk().split(",");
            for (int i = 0; i < jksb.length; i++) {
                camera = new BusOperationplanCamera();
                camera.setOperationId(entity.getId());
                camera.setCameraNum(jksb[i]);
                BusOperationplanCamera rel = dbContext.entityLoadBySql(BusOperationplanCamera.class, "select id as camera_id,camera_type,camera_name from bus_camera where camera_num= ?", par(jksb[i]));
                if (rel != null) {
                    camera.setCameraId(rel.getCameraId());
                    camera.setCameraType(rel.getCameraType());
                    camera.setCameraName(rel.getCameraName());
                    camera.setId(getUUID());
                }
                camera.insert();
            }
        } else {
            //保存作业计划
            String uuid = getUUID();
            entity.setId(uuid);
            entity.setCreateBy(user);
            entity.setDelFlag("0");
            entity.setStatus("0");
            entity.insert();
            //保存作业计划相关人员
            BusOperationplanPerson person = null;
            String[] zyry = entity.getZyryid().split(",");
            for (int i = 0; i < zyry.length; i++) {
                person = new BusOperationplanPerson();
                person.setOperationId(uuid);
                person.setStatus("1");
                person.setPersonId(zyry[i]);
                person.insert();
            }
            //保存作业计划相关监控设备
            BusOperationplanCamera camera = null;
            String[] jksb = entity.getZysbjk().split(",");
            for (int i = 0; i < jksb.length; i++) {
                camera = new BusOperationplanCamera();
                camera.setOperationId(uuid);
                camera.setCameraNum(jksb[i]);
                BusOperationplanCamera rel = dbContext.entityLoadBySql(BusOperationplanCamera.class, "select id as camera_id,camera_type,camera_name from bus_camera where camera_num= ?", par(jksb[i]));
                if (rel != null) {
                    camera.setCameraId(rel.getCameraId());
                    camera.setCameraType(rel.getCameraType());
                    camera.setCameraName(rel.getCameraName());
                    camera.setId(getUUID());
                }
                camera.insert();
            }
        }
        return true;
    }

    public Object remove(String id) {
        return dbContext.exe("update bus_operationplan set DEL_FLAG='1' where id in('" + id.replaceAll(",", "','") + "')");
    }
    public Object wzListDelete(String id) {
        return dbContext.exe("delete from t_event_record where id in('" + id.replaceAll(",", "','") + "')");
    }


    @Transactional
    public Object startORover(String id, String status, String sfId) {
        JSONObject camera = null;
        if (StrUtil.isNotEmpty(sfId)) {
            String user = (String) session.getAttribute("usercode");
            String[] ids = id.split(",");
            String[] sfIds = sfId.split(",");
            String sf=dbContext.qryString("select GROUP_CONCAT(DISTINCT algorithmKey) algorithmKey from t_sys_event_name where algorithmMsg in('" + sfId.replaceAll(",", "','") + "')");
            for (int i = 0; i < ids.length; i++) {
                // 给行为识别平台推送任务开始信息
                String codes = dbContext.qryString("select GROUP_CONCAT(camera_num) camera_num from bus_operationplan_camera where operation_id = ?", par(ids[i]));
                CameraAlgorithm bean=null;
                dbContext.exe("delete from camera_algorithm where camera_num=? ",par(id));
                for (int k=0;k<sfIds.length;k++){
                    bean=new CameraAlgorithm();
                    bean.setCameraNum(codes);
                    bean.setAlgorithm(sfIds[k]);
                    if (bean!=null){
                        bean.insert();
                    }
                }
                if (StrUtil.isNotEmpty(codes)) {
                    String[] camera_codes = codes.split(",");
                    for (int c = 0; c < camera_codes.length; c++) {
                        camera = new JSONObject();
                        camera.set("CAMERA_CODE", camera_codes[i]);
                        camera.set("STREAM", cameraControlService.play(camera_codes[i]));
                        String body = StrUtil.format("{\"msgType\":201,\"data\":{\"CAMERA_ADD\":\"{}\"," +
                                "\"CAMERA_REMOVE\":\"{}\",\"ALGORITHM_LIST\":\"{}\"}}", camera, "", sf);
                        LOG.info("推送工作计划开始信息：{}", body);
                        String rs = null;
                        try {
                            rs = HttpUtil.post(environment.getProperty("local.api"), body, 3000);
                            LOG.debug(rs);
                        } catch (Exception e) {
                            return "行为识别接口服务无法访问";
                        }
                    }
                }
                if (sfIds.length > 0) {
                    dbContext.exe("delete from bus_operationplan_algorithm where operation_id=? ", par(ids[i]));
                    for (int j = 0; j < sfIds.length; j++) {
                        dbContext.exe("INSERT INTO bus_operationplan_algorithm(operation_id, algorithm_id, create_by) VALUES (?,?,?) ", par(ids[i], sfIds[j], user));
                    }
                }

            }
        }
        return dbContext.exe("update bus_operationplan set status=? where id in('" + id.replaceAll(",", "','") + "')", par(status));
    }

    @Transactional
    public Object wzqr(String id, String state, String type) {
        if ("3".equals(type)) {
            return dbContext.exe("update t_event_offline set state=? where id = ? ", par(state, id));
        } else {
            return dbContext.exe("update t_event_record set state=? where id = ? ", par(state, id));
        }
    }

    public Object load(String id) {
        if (StrUtil.isNotEmpty(id)) {
            return dbContext.qryMap("SELECT\n" +
                    "\tID, \n" +
                    "\tTEAM, \n" +
                    "\tCOORDINATE, \n" +
                    "\tPEOPLE_NUM, \n" +
                    "\tIS_ELECTRIFIED, \n" +
                    "\tJOB_CONTENT, \n" +
                    "\tWORKING_DAYS, \n" +
                    "\tDATE_FORMAT(START_TIME,'%Y-%m-%d %H:%i:%s') START_TIME, \n" +
                    "\tWORKING_TYPE, \n" +
                    "\tDATE_FORMAT(END_TIME,'%Y-%m-%d %H:%i:%s') END_TIME, \n" +
                    "\tMAJOR_TYPE, \n" +
                    "\tOPERATIONAL_RISK_LEVEL, \n" +
                    "\tPOWER_RISK_LEVEL, \n" +
                    "\tVOLTAGE_LEVEL, \n" +
                    "\tOPERATION_CHARGE_PERSON, \n" +
                    "\tOPERATION_CHARGE_PERSON_PHONE, \n" +
                    "\tOPERATION_CHARGE_PERSON_ID, \n" +
                    "\tSTATUS, \n" +
                    "\tCREATE_DATE, \n" +
                    "\tCREATE_BY, \n" +
                    "\tUPDATE_BY, \n" +
                    "\tUPDATE_DATE, \n" +
                    "\tDEL_FLAG, \n" +
                    "\tREMARKS, \n" +
                    "\tOPERATION_LOCATION, \n" +
                    "\tCONSTRUCTION_MANAGEMENT_UNIT, \n" +
                    "\tCONSTRUCTION_UNIT, \n" +
                    "\tCONSTRUCTION_UNIT_TYPE, \n" +
                    "\tMAIN_UNIT_NUM, \n" +
                    "\tOUTSOURCING_UNIT_NUM, \n" +
                    "\tSUPERVISE_UNIT_NUM, \n" +
                    "\tSAFETY_CHARGE_PERSON, \n" +
                    "\tSAFETY_CHARGE_PERSON_ID, \n" +
                    "\tSAFETY_CHARGE_PERSON_PHONE, \n" +
                    "\tSPEICAL_PERSON, \n" +
                    "\tSPEICAL_ID, \n" +
                    "\tSPEICAL_PERSON_PHONE, \n" +
                    "\tVIOLATION, \n" +
                    "\tINTEGRITY, \n" +
                    "\tERR_MSG\n" +
                    "FROM\n" +
                    "\tbus_operationplan\n" +
                    "WHERE\n" +
                    "\tid = ? ", par(id));
        } else {
            return null;
        }
    }

    public Object loadZyry(String id) {
        if (StrUtil.isNotEmpty(id)) {
            return dbContext.qryMapList("select person_id from bus_operationplan_person where operation_id=?", par(id));
        } else {
            return null;
        }
    }

    public Object loadZysb(String id) {
        if (StrUtil.isNotEmpty(id)) {
            return dbContext.qryMapList("select camera_num from bus_operationplan_camera where operation_id=?", par(id));
        } else {
            return null;
        }
    }

    public Object loadSgdw(String id) {
        if (StrUtil.isNotEmpty(id)) {
            return dbContext.qryMapList("SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(b.CONSTRUCTION_UNIT,',',h.help_topic_id+1),',',-1)\n" +
                    "unit FROM bus_operationplan  b\n" +
                    "JOIN mysql.help_topic h on h.help_topic_id<((LENGTH(b.CONSTRUCTION_UNIT)-LENGTH(REPLACE(b.CONSTRUCTION_UNIT,',',''))+1))\n" +
                    "where b.id=? ", par(id));
        } else {
            return null;
        }
    }

    public Object OperationplanList() {
        return dbContext.qryMapList("select ID id,JOB_CONTENT name from bus_operationplan where 1=1 and DEL_FLAG='0' order by start_time desc");
    }

    public Object loadAllzyCoordinates(String status) {
        return dbContext.qryMapList("select ID id,COORDINATE coordinate from bus_operationplan where 1=1 and DEL_FLAG='0' ",
                noBlank(" and status = ? ", status));
    }


    public Object wzList(TEventRecord tEventRecord) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT ");
        sb.append("t.id, ");
        sb.append("t.planid, ");
        sb.append("t.eventid, ");
        sb.append("IFNULL(t1.eventName,'未知违章类型') eventName, ");
        sb.append("IFNULL(b.enterprise_name,'未知作业单位') construction_name, ");
        sb.append("t.algorithmId, ");
        sb.append("IFNULL(t.algorithmKind,'未知违章事件') algorithmKind, ");
        sb.append("t.imagePath, ");
        sb.append("t.videoPath, ");
        sb.append("c.label state, ");
        sb.append("t.createTime ");
        sb.append("FROM ");
        sb.append("t_event_record t ");
        sb.append("LEFT JOIN t_sys_event_name t1 ON t1.algorithmMsg = t.algorithmId ");
        sb.append("LEFT JOIN bus_operationplan p on p.id=t.planid ");
        sb.append("LEFT JOIN bus_unit b on p.CONSTRUCTION_UNIT=b.id ");
        sb.append("LEFT JOIN t_sys_dict c on t.state=c.value and c.type='dict_state' ");
        sb.append("WHERE 1=1 ");
        if (StrUtil.isEmpty(tEventRecord.getPlanid())) {
            sb.append(" and t.planid = 'xxxx'");
        }
        return builder(sb.toString(), " order by t.state,t.createTime desc");
    }

    public Object wzCount(String planid) {
        return dbContext.qryMap("select t1.ID,(select count(1) from t_event_record t where t.planid=t1.ID and t.state='1') wgCount," +
                "(select count(1) from t_event_record t where t.planid=t1.ID and t.state='0') dqrwgCount from bus_operationplan t1 where t1.ID=? ", par(planid));
    }

    public Object loadPlanInfo(String plan_id) {
        return dbContext.qryMap("select a.ID, JOB_CONTENT, CONSTRUCTION_MANAGEMENT_UNIT, START_TIME, END_TIME, b.LABEL as RISK_LEVEL " +
                " from bus_operationplan a left join t_sys_dict b on b.TYPE = 'dict_zyfxdj' and b.VALUE = a.OPERATIONAL_RISK_LEVEL " +
                " where a.ID =?", par(plan_id));
    }

    public Object loadCamera(String plan_id) {
        return dbContext.qryMapList("select b.camera_name, b.camera_num, b.camera_type, b.stream from bus_operationplan_camera a " +
                "left join bus_camera b on a.camera_num = b.camera_num where a.operation_id =?", par(plan_id));
    }

    public List<Map<String, Object>> cameraCheck(String cameraid, String startTime, String endTime, String operationid) {
        return dbContext.qryMapList("SELECT" +
                        " a.camera_id, b.JOB_CONTENT, b.START_TIME, b.END_TIME " +
                        "FROM " +
                        " bus_operationplan_camera a " +
                        " left join bus_operationplan b on a.operation_id = b.id " +
                        "where " +
                        " 1=1" +
                        " and b.STATUS !=2 and b.DEL_FLAG=0 " +
                        " and a.camera_num in('" + cameraid.replaceAll(",", "','") + "')" +
                        " and ((b.START_TIME BETWEEN '" + startTime + "' and '" + endTime + "') " +
                        " or (b.END_TIME BETWEEN '" + startTime + "' and '" + endTime + "') " +
                        " or (b.START_TIME<'" + startTime + "' and b.END_TIME>'" + endTime + "')) ",
                noBlank(" and b.id !=?", operationid));
    }

    public Object loadRyxx(String plan_id, String lastTime) {
        return dbContext.qryMapList("select ifnull(b.pname, '陌生人') as name, a.createTime, a.userKind, b.id faceId, a.faceImage,ifnull(u.enterprise_name,'未知') unit,ifnull(b.phone,'') phone,ifnull(b.driver_point,'') driver_point from t_person_record a " +
                        "left join bus_person b on a.userid = b.ID left join bus_unit u on u.id= b.unit where 1=1 ",
                noBlank(" and a.createTime > ?", lastTime),
                " and planid = ? order by a.createTime", par(plan_id));
    }

    public Object loadWzxx(String plan_id, String lastTime) {
        return dbContext.qryMapList("select a.planid,a.id, ifnull(b.eventName,'未知违章类型') as eventName, a.imagePath, a.videoPath, a.state, a.createTime " +
                        "from t_event_record a left join t_sys_event_name b on a.algorithmId = b.algorithmMsg where 1=1 and a.state='0' ",
                noBlank(" and a.createTime > ?", lastTime),
                " and planid = ? order by a.createTime", par(plan_id));
    }

    public Object echartsAllplan() {
        StringBuffer sb = new StringBuffer();
        sb.append(" select GROUP_CONCAT(c.sum SEPARATOR ',') as sum from (SELECT DISTINCT m.LABEL,m.TYPE,IFNULL(s.sum,0) as sum from t_sys_dict AS m ");
        sb.append(" LEFT JOIN ");
        sb.append(" (select t.LABEL,count(t.LABEL) as sum from bus_operationplan p1  ");
        sb.append(" LEFT JOIN t_sys_dict t on p1.WORKING_TYPE=t.LABEL and t.TYPE='dict_gzxz' ");
        sb.append(" where p1.STATUS='0' GROUP BY t.LABEL) AS s ON s.LABEL=m.LABEL WHERE m.TYPE='dict_gzxz' ORDER BY m.LABEL DESC) AS c ");
        sb.append(" UNION ALL ");
        sb.append(" select GROUP_CONCAT(c.sum SEPARATOR ',') as sum from (SELECT DISTINCT m.LABEL,m.TYPE,IFNULL(s.sum,0) as sum from t_sys_dict AS m ");
        sb.append(" LEFT JOIN ");
        sb.append(" (select t.LABEL,count(t.LABEL) as sum from bus_operationplan p1  ");
        sb.append(" LEFT JOIN t_sys_dict t on p1.WORKING_TYPE=t.LABEL and t.TYPE='dict_gzxz' ");
        sb.append(" where p1.STATUS='1' GROUP BY t.LABEL) AS s ON s.LABEL=m.LABEL WHERE m.TYPE='dict_gzxz' ORDER BY m.LABEL DESC) AS c ");
        sb.append(" UNION ALL ");
        sb.append(" select GROUP_CONCAT(c.sum SEPARATOR ',') as sum from (SELECT DISTINCT m.LABEL,m.TYPE,IFNULL(s.sum,0) as sum from t_sys_dict AS m ");
        sb.append(" LEFT JOIN ");
        sb.append(" (select t.LABEL,count(t.LABEL) as sum from bus_operationplan p1  ");
        sb.append(" LEFT JOIN t_sys_dict t on p1.WORKING_TYPE=t.LABEL and t.TYPE='dict_gzxz' ");
        sb.append(" where p1.STATUS='2' GROUP BY t.LABEL) AS s ON s.LABEL=m.LABEL WHERE m.TYPE='dict_gzxz' ORDER BY m.LABEL DESC) AS c ");
        return dbContext.qryMapList(sb.toString());
    }

    public Object loadRy() {
        return dbContext.qryMapList("select id as value,pname name from bus_person ");
    }

    public Object importData(MultipartFile file, HttpServletRequest request, HttpServletResponse response) {
        MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
        JSONObject json=new JSONObject();
        long code=200;
        String msg="";
        if (file.isEmpty()) {
            try {
                throw new Exception("文件不存在！");
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        InputStream in = null;
        List<List<Object>> listob = null;
        List<Object> dataList = null;
        try {
            in = file.getInputStream();
            ExcelReader read = ExcelUtil.getReader(in, 0);
            listob=read.read(2,read.getRowCount());
            System.out.println(listob.toString());
        } catch (IOException e) {
            e.printStackTrace();
        }
        Date date1 = null;
        Date date2 = null;
        String dydj = "";
        String zyfxdj = "";
        String dwfxdj = "";
        int count=0;
        try {
            for (int i = 0; i < listob.size(); i++) {
                dataList = listob.get(i);
                if (dataList.size() > 20) {
                    if (dataList.get(0)==null){
                        continue;
                    }
                    if (dataList.get(5).toString().length()>500){
                        json.put("code",400);
                        json.put("msg","工作内容超出最大限制500字!");
                        return json;
                    }
                    date1 = cn.hutool.core.date.DateUtil.parse(dataList.get(2).toString(),"yyyy-MM-dd HH:mm:ss");
                    date2 = cn.hutool.core.date.DateUtil.parse(dataList.get(3).toString(),"yyyy-MM-dd HH:mm:ss");

                    BusOperationplan plan = null;
                    plan = new BusOperationplan();
                    plan.setStartTime(new Timestamp(date1.getTime()));
                    plan.setEndTime(new Timestamp(date2.getTime()));
                    plan.setOperationLocation(dataList.get(4).toString());
                    plan.setJobContent(dataList.get(5).toString());
                    if (dataList.get(6).toString().equals("无")) {
                        plan.setIsElectrified("0");
                    } else {
                        plan.setIsElectrified("1");
                    }
                    plan.setWorkingType(dataList.get(7).toString());
                    if (StrUtil.isNotEmpty(dataList.get(8).toString())) {
                        dydj = dbContext.qryString("select IFNULL(max(VALUE),'99') AS dydj from t_sys_dict where type='dict_dydj' and label=? ", par(dataList.get(8).toString()));
                    } else {
                        dydj = "99";
                    }
                    plan.setVoltageLevel(dydj);
                    if (StrUtil.isNotEmpty(dataList.get(10).toString())) {
                        zyfxdj = dbContext.qryString("select IFNULL(max(VALUE),'99') AS fxdj from t_sys_dict where type='dict_zyfxdj' and label=? ", par(dataList.get(10).toString()));
                    } else {
                        zyfxdj = "99";
                    }
                    plan.setOperationalRiskLevel(zyfxdj);
                    if (StrUtil.isNotEmpty(dataList.get(11).toString())) {
                        dwfxdj = dbContext.qryString("select IFNULL(max(VALUE),'99') AS fxdj from t_sys_dict where type='dict_dwfxdj' and label=? ", par(dataList.get(11).toString()));
                    } else {
                        dwfxdj = "99";
                    }
                    plan.setPowerRiskLevel(dwfxdj);
                    plan.setConstructionManagementUnit(dataList.get(13).toString());
                    plan.setConstructionUnit(dbContext.qryString("select IFNULL(max(id),'') from bus_unit where enterprise_name=? ", par(dataList.get(15).toString())));
                    plan.setOperationChargePersonId(dbContext.qryString("select IFNULL(max(id),'') from bus_person where pname=? ", par(dataList.get(18).toString())));
                    plan.setOperationChargePerson(dataList.get(18).toString());
                    plan.setOperationChargePersonPhone(dataList.get(19).toString());

                    plan.setSpeicalId(dbContext.qryString("select IFNULL(max(id),'') from bus_person where pname=? ", par(dataList.get(21).toString())));
                    plan.setSpeicalPerson(dataList.get(21).toString());
                    plan.setSpeicalPersonPhone(dataList.get(22).toString());

                    plan.setSafetyChargePersonId(dbContext.qryString("select IFNULL(max(id),'') from bus_person where pname=? ", par(dataList.get(24).toString())));
                    plan.setSafetyChargePerson(dataList.get(24).toString());
                    plan.setSafetyChargePersonPhone(dataList.get(25).toString());
                    plan.setDelFlag("0");
                    plan.setStatus("0");
                    plan.setId(getUUID());
                    plan.insert();
                    count++;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        json.put("code",200);
        json.put("size",count);
        return json;
    }
}
